
using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Text;
using net.windward.utils.ado.DB2;
using NUnit.Framework;

namespace net.windward.utils.ado
{
    [TestFixture]
    public class TestDB2Provider
    {
        private string serverName;
        private string databaseName;
        private string username;
        private string password;

        readonly DbProviderFactory providerFactory = DbProviderFactories.GetFactory(WrDB2Vendor.FactoryClass);

        [SetUp]
        public void Init()
        {
            NameValueCollection appSettings = ConfigurationManager.AppSettings;
            serverName = appSettings["DB2Server"];
            databaseName = appSettings["DB2Database"];
            username = appSettings["DB2Username"];
            password = appSettings["DB2Password"];
        }

        [Test]
        public void TestCreateProvider()
        {

            WrDB2Vendor vendor = (WrDB2Vendor)WrProviderFactories.CreateProvider("DB2 Server name", null, WrDB2Vendor.FactoryClass);
            Assert.IsNotNull(vendor);
            Assert.AreEqual(true, vendor.CanEnumerateDatabases);
            Assert.AreEqual(true, vendor.CanEnumerateServers);
            //			Assert.AreEqual(true, vendor.CanLaunchAdministrator);
            Assert.AreEqual(false, vendor.CanRunScripts);
            Assert.AreEqual(true, vendor.CanUseTrustedConnection);
            Assert.AreEqual(WrVendor.DATABASE_MODE.REQUIRED, vendor.DatabaseMode);
            Assert.AreEqual(false, vendor.HasProviders);

            Assert.AreEqual("Database=def;User ID=123;Password=456;Server=abc",
                vendor.ConnectionString("abc", "def", new WrCredentials("123", "456"), true));
            Assert.AreEqual("Database=def;Server=abc",
                vendor.ConnectionString("abc", "def", new WrCredentials(), true));

            // enum servers - make sure has test one
            IWrServer[] servers = vendor.GetServers();

			/* bugbug - doesn't find apollo
            Assert.IsTrue(servers.Length > 0);
            bool gotServer = false;
            foreach (IWrServer server in servers)
            {
                Assert.IsTrue(server is WrDB2Server);
                if (server.Name.ToLower().Contains(serverName.ToLower()))
                    gotServer = true;
            }
            Assert.IsTrue(gotServer);
			*/
        }

        [Test]
        public void TestSampleEnumeration()
        {

            // enum databases, make sure has test one
            WrDB2Server server = new WrDB2Server(providerFactory, serverName, new string[] { "SAMPLE" });
            Assert.AreEqual(serverName, server.Name);
            IWrDatabase[] databases = server.GetDatabases(new WrCredentials(username, password));
            Assert.IsTrue(databases.Length > 0);
            WrDB2Database db = null;
            foreach (IWrDatabase dbOn in databases)
            {
                Assert.IsTrue(dbOn is WrDB2Database);
                if (dbOn.Name == databaseName)
                    db = (WrDB2Database)dbOn;
            }
            Assert.IsNotNull(db);

            // SAMPLE tables
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
			Assert.AreEqual(14, db.Tables.Length);
			Assert.AreEqual("DADMIN.CL_SCHED", db.Tables[1].FullName);
			Assert.IsFalse(db.Tables[0].IsSystem);
			Assert.AreEqual("DADMIN.STAFF", db.Tables[13].FullName);
			Assert.IsFalse(db.Tables[10].IsSystem);

			// views
			Assert.AreEqual(20, db.Views.Length);

			// SAMPLE.EMPLOYEE columns
			Assert.AreEqual(14, db.Tables.Length);
			WrColumn[] cols = db.Tables[3].Columns;
			Assert.AreEqual(14, cols.Length);
			Assert.AreEqual("EMPNO", cols[0].FullName);
			Assert.AreEqual(DbType.String, cols[0].DbType);
			Assert.AreEqual("COMM", cols[13].FullName);
			Assert.AreEqual(DbType.Decimal, cols[13].DbType);

			// stored procedures
			Assert.AreEqual(1, db.StoredProcedures.Length);

			// SAMPLE.EMP_PHOTO columns - with metadata
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			Assert.AreEqual(14, db.Tables.Length);
			Assert.AreEqual("DADMIN.EMP_PHOTO", db.Tables[6].FullName);
			WrColumn[] colsFKPK = db.Tables[6].Columns;
			Assert.AreEqual(3, colsFKPK.Length);
			Assert.AreEqual("EMPNO", colsFKPK[0].FullName);
			Assert.AreEqual("DADMIN.EMPLOYEE.EMPNO", colsFKPK[0].PrimaryKey);


			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.SYSTEM, null);
			Assert.AreEqual(140, db.Tables.Length);
			Assert.AreEqual(242, db.Views.Length);
			Assert.AreEqual(161, db.StoredProcedures.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.ALL, null);
			Assert.AreEqual(146, db.Tables.Length);
			Assert.AreEqual(262, db.Views.Length);
			Assert.AreEqual(162, db.StoredProcedures.Length);


            // KailuaTest - all types
			db = new WrDB2Database(providerFactory, serverName, "WRTEST", new WrCredentials(username, password));
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
        	cols = db.Tables[0].Columns;
            Assert.AreEqual(18, cols.Length);
            // bugbug - assert each name & DbType. DbType is the key test
        }

        //		[Test]
        public void TestTrusted()
        {

            WrDB2Server server = new WrDB2Server(providerFactory, serverName, new string[] { databaseName });
            IWrDatabase[] dbUsername = server.GetDatabases(new WrCredentials(username, password));
            IWrDatabase[] dbTrusted = server.GetDatabases(new WrCredentials());
            Assert.AreEqual(dbUsername.Length, dbTrusted.Length);
            for (int ind = 0; ind < dbUsername.Length; ind++)
                Assert.AreEqual(dbUsername[ind].Name, dbTrusted[ind].Name);

			dbUsername[0].LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
			dbTrusted[0].LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
        	WrTable[] tblUsername = dbUsername[0].Tables;
        	WrTable[] tblTrusted = dbTrusted[0].Tables;
            Assert.AreEqual(tblUsername.Length, tblTrusted.Length);
            for (int ind = 0; ind < tblUsername.Length; ind++)
                Assert.AreEqual(tblUsername[ind].FullName, tblTrusted[ind].FullName);
        }

        // note - cannot insert a timestamp
        // bugbug - DATE type cannot handle a DateTime var - must use a date string
        private readonly string[] colNames = {"KBIGINT", "KBLOB", "KCHARACTER", "KCLOB", "KDATE", "KDECIMAL", "KDOUBLE",
				"KINTEGER", "KLONGVARCHAR", "KREAL", "KSMALLINT", "KTIME", "KVARCHAR", "KBYTE", "KBOOLEAN", "KDATETIME"};
        private readonly object[] colValues = {1, new byte[4], "dave", "zoe", DateTime.Now.ToShortDateString(), new Decimal(23.45), 67.89,
			12, "shirley", 23.45f, 6, DateTime.Now.TimeOfDay, "cody", 57, true, DateTime.Now};

        [Test]
        public void TestInsert()
        {
            using (WrConnection conn = new WrConnection("DB2Write"))
            {
                conn.Open();

                // empty it to start the same
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "delete from AllTypes";
                    cmd.ExecuteNonQuery();
                }

                // test get the PK back
                long[] pk = new long[2];
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "insert into AllTypes (KVARCHAR) values (?)";
                    cmd.Set(0, "dave");
                    pk[0] = cmd.ExecuteInsert("pk");
                    Assert.IsTrue(pk[0] >= 0);
                }

                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "insert into AllTypes (KVARCHAR) values (?)";
                    cmd.Set(0, "shirley");
                    pk[1] = cmd.ExecuteInsert("pk");
                    Assert.IsTrue(pk[1] > pk[0]);
                }

                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "select * from AllTypes where pk = ?";
                    cmd.Set(0, pk[0]);
                    using (WrDataReader reader = cmd.WrExecuteReader())
                    {
                        for (int ind = 0; ind < colNames.Length; ind++)
                        {
                            object obj = reader.GetDataTypeName(ind);
                            obj = reader.GetFieldType(ind);
                            obj = reader.GetName(ind);
                            obj = null;
                        }
                    }
                }

                // test insert each var type
                for (int ind = 0; ind < colNames.Length; ind++)
                {
                    using (WrCommand cmd = conn.CreateWrCommand())
                    {
                        cmd.CommandText = "insert into AllTypes (" + colNames[ind] + ") values (?)";
                        cmd.Set(0, colValues[ind]);
                        cmd.ExecuteInsert("pk");
                    }
                }

                // write all together
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    StringBuilder command = new StringBuilder("insert into AllTypes (");
                    for (int ind = 0; ind < colNames.Length; ind++)
                        command.Append(colNames[ind] + ",");
                    command.Remove(command.Length - 1, 1);
                    command.Append(") values (");
                    for (int ind = 0; ind < colNames.Length; ind++)
                        command.Append("?,");
                    command.Remove(command.Length - 1, 1);
                    command.Append(")");
                    cmd.CommandText = command.ToString();

                    for (int ind = 0; ind < colNames.Length; ind++)
                        cmd.Set(ind, colValues[ind]);

                    pk[0] = cmd.ExecuteInsert("pk");
                }

                // test read each var type
                byte[] data = new byte[20];
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "select * from AllTypes where pk = ?";
                    cmd.Set(0, pk[0]);
                    using (WrDataReader reader = cmd.WrExecuteReader())
                    {
                        Assert.IsTrue(reader.Read());
                        Assert.AreEqual(pk[0], reader.GetInt64(0));
                        Assert.AreEqual(colValues[0], reader.GetInt64(1));
                        Assert.AreEqual(4, reader.GetBytes(2, 0, data, 0, 4));
                        Assert.AreEqual("dave      ", reader.GetString(3));
                        Assert.AreEqual(colValues[3], reader.GetString(4));
                        object obj = reader.GetValue(5);
                        Assert.AreEqual(colValues[4], reader.GetDateTime(5).Date.ToShortDateString());
                        //bugbug						Assert.AreEqual(colValues[5], reader.GetDecimal(6));
                        Assert.AreEqual(colValues[6], reader.GetDouble(7));
                        Assert.AreEqual(colValues[7], reader.GetInt32(8));
                        Assert.AreEqual(colValues[8], reader.GetString(9));
                        Assert.AreEqual(colValues[9], reader.GetFloat(10));
                        Assert.AreEqual(colValues[10], reader.GetInt16(11));
                        Assert.IsNotNull((TimeSpan)reader.GetValue(12));
                        Assert.IsTrue(reader.GetTimestamp(13) > 0);
                        Assert.AreEqual(colValues[12], reader.GetString(14));
                        Assert.AreEqual(colValues[13], reader.GetByte(15));
                        Assert.AreEqual(colValues[14], reader.GetBoolean(16));
                        Assert.AreEqual(((DateTime)colValues[15]).Date, reader.GetDateTime(17).Date);


                        // remaining calls
                        Assert.IsFalse(reader.IsDBNull(1));
                        Assert.AreEqual("KBLOB", reader.GetName(2));
                        Assert.AreEqual("CHAR", reader.GetDataTypeName(3));
                        Assert.AreEqual(typeof(Decimal), reader.GetFieldType(6));
                        Assert.AreEqual(7, reader.GetOrdinal("KDOUBLE"));
                        Assert.AreEqual(18, reader.FieldCount);
                        Assert.AreEqual("cody", reader[14]);
                        Assert.AreEqual("cody", reader["kvarchar"]);

                        /* bugbug - fails - no idea why
                        object[] all = new object[18];
                        Assert.AreEqual(18, reader.GetValues(all));
                        Assert.AreEqual(pk[0], all[0]);
                        Assert.AreEqual("cody", all[14]);
                        */
                    }
                }

                // test select top N
                using (WrCommand cmd = conn.CreateWrCommand())
                {
                    cmd.CommandText = "  select   * from AllTypes order by pk DESC  top  1  ";
                    Assert.AreEqual("select   * from AllTypes order by pk DESC fetch first 1 rows only", cmd.CommandText);
                    using (WrDataReader reader = cmd.WrExecuteReader())
                    {
                        Assert.IsTrue(reader.Read());
                        Assert.AreEqual(pk[0], reader.GetInt64(0));
                    }
                }
            }
        }

        //		[Test]
        public void TestLaunch()
        {

            WrDB2Vendor provider = (WrDB2Vendor)WrProviderFactories.CreateProvider("My Sql Server name", null, WrDB2Vendor.FactoryClass);
            Assert.IsTrue(provider.RunAdministrator());
        }

    }
}
